﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Physis.Core.Sponsor {

    public class SponsorFactory : FactoryBase {

        #region Constructors

        public SponsorFactory (Application forApplication) : base (forApplication) { return; }

        #endregion 
        

        #region Factories

        public List<SponsorType> SponsorTypes (Boolean useCaching = false) {

            List<SponsorType> sponsorTypes = new List<SponsorType> ();

            String cacheKey = "Application.SponsorTypes.All";

            ClearLastException ();

            try {

                if (useCaching) { sponsorTypes = (List<SponsorType>)CacheManager.GetObject (cacheKey); }

                else { sponsorTypes = null; }

                if (sponsorTypes == null) {

                    sponsorTypes = new List<SponsorType> ();

                    String selectStatement = "SELECT SponsorType.* ";

                    selectStatement += "  FROM SponsorType";

                    selectStatement += "  ORDER BY SponsorType.Name";


                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        SponsorType sponsorType = new SponsorType (Application);

                        sponsorType.MapDataFields (currentDataRow);

                        CacheManager.CacheObject ("Application.SponsorType." + sponsorType.Id, sponsorType, CacheExpirationReference);  // CACHE INDIVIDUAL ROLE 

                        sponsorTypes.Add (sponsorType);

                    }

                    CacheManager.CacheObject (cacheKey, sponsorTypes, CacheExpirationReference); // CACHE THE WHOLE LIST

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsorTypes;

        }

        public SponsorType SponsorType (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.SponsorType." + id;

            SponsorType sponsorType = null;

            ClearLastException ();

            try {

                if (useCaching) { sponsorType = (SponsorType)CacheManager.GetObject (cacheKey); }

                if (sponsorType == null) {

                    sponsorType = new SponsorType (Application, id);

                    CacheManager.CacheObject (cacheKey, sponsorType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsorType;

        }

        public SponsorType SponsorType (String name, Boolean useCaching = false) {

            if (String.IsNullOrWhiteSpace (name)) { return null; }

            String cacheKey = "Physis.Application.SponsorType." + name;

            SponsorType sponsorType = null;

            ClearLastException ();

            try {

                if (useCaching) { sponsorType = (SponsorType)CacheManager.GetObject (cacheKey); }

                if (sponsorType == null) {

                    sponsorType = new SponsorType (Application, name);

                    CacheManager.CacheObject (cacheKey, sponsorType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsorType;

        }


        public List<Sponsor> Sponsors (Boolean useCaching = false) {

            List<Sponsor> sponsors = new List<Sponsor> ();

            String cacheKey = "Application.Core.Sponsor.All"; 

            ClearLastException ();

            try {

                if (useCaching) { sponsors = (List<Sponsor>)CacheManager.GetObject (cacheKey); }

                else { sponsors = null; }

                if (sponsors == null) {

                    sponsors = new List<Core.Sponsor.Sponsor> ();

                    String selectStatement = "SELECT * FROM Sponsor JOIN Entity ON Sponsor.EntityId = Entity.Id ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        Sponsor sponsor = new Sponsor (Application);

                        sponsor.MapDataFields (currentDataRow);

                        sponsors.Add (sponsor);

                    }

                    CacheManager.CacheObject (cacheKey, sponsors, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsors;

        }
        
        public Sponsor Sponsor (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.Sponsor." + id;

            Sponsor sponsor = null;

            ClearLastException ();

            try {

                if (useCaching) { sponsor = (Sponsor)CacheManager.GetObject (cacheKey); }

                if (sponsor == null) {

                    sponsor = new Sponsor (Application, id);

                    CacheManager.CacheObject (cacheKey, sponsor, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsor;

        }


        public List<SponsorEnrollment> SponsorEnrollments (Int64 sponsorId, Boolean useCaching = false) {

            List<SponsorEnrollment> sponsorEnrollments = new List<SponsorEnrollment> ();

            String cacheKey = "Application.Core.Sponsor.SponsorEnrollments." + sponsorId;

            ClearLastException ();

            try {

                if (useCaching) { sponsorEnrollments = (List<SponsorEnrollment>)CacheManager.GetObject (cacheKey); }

                else { sponsorEnrollments = null; }

                if (sponsorEnrollments == null) {

                    sponsorEnrollments = new List<Core.Sponsor.SponsorEnrollment> ();

                    String selectStatement = "SELECT * FROM SponsorEnrollment WHERE SponsorId = " + sponsorId;

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        SponsorEnrollment sponsor = new SponsorEnrollment (Application);

                        sponsor.MapDataFields (currentDataRow);

                        sponsorEnrollments.Add (sponsor);

                    }

                    CacheManager.CacheObject (cacheKey, sponsorEnrollments, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return sponsorEnrollments;

        }

        #endregion 


        #region Sponsor Query 

        private String SponsorsQuerySqlStatement (List<Data.FilterDescriptor> filters) {

            String sqlStatement = String.Empty;


            String selectClause = String.Empty;

            String fromClause = String.Empty;

            String joinStatement = String.Empty;

            String whereClause = String.Empty;

            String orderByClause = String.Empty;



            selectClause = "SELECT Sponsor.* /*_CUSTOM_FIELD_INSERT_*/ ";

            selectClause = selectClause + "\r\n /*_CALCULATED_FIELDS_BEGIN_*/ ";

            selectClause = selectClause + "\r\n /*_CALCULATED_FIELDS_END_*/ ";


            fromClause = "  FROM Sponsor \r\n /*_CUSTOM_FILTER_JOINS_INSERT_*/";

            whereClause = "  WHERE (1 = 1) /*_CUSTOM_FILTER_INSERT_*/";


            if (filters == null) { filters = new List<Data.FilterDescriptor> (); }

            foreach (Data.FilterDescriptor currentFilter in filters) {

                String criteriaString = String.Empty;

                switch (currentFilter.PropertyPath.ToLower ()) {

                    case "name":

                        // DETERMINE IF WE ARE ALREADY JOINING TO THE ENTITY TABLE FOR THE ADDITIONAL FILTERS

                        if (!fromClause.Contains ("JOIN Entity")) {

                            fromClause += "    JOIN Entity ON Sponsor.EntityId = Entity.Id \r\n";

                        }

                        criteriaString = currentFilter.SqlCriteriaString ("Entity"); 
                        
                        break;

                    default:

                        if (currentFilter.PropertyPath.Split ('.').Length > 1) {

                            #region Related Object Properties

                            // HEIRARCHY FILTER CRITERIA 

                            switch (currentFilter.PropertyPath.ToLower ()) {

                                default: break; // PLACE HOLDER DO NOTHING

                            }

                            #endregion

                        }

                        else { criteriaString = currentFilter.SqlCriteriaString ("Sponsor"); } // ASSUMES DEFAULT TABLE FILTERING

                        break;

                } // switch (currentFilter.PropertyPath) {

                if (!String.IsNullOrEmpty (criteriaString)) {

                    whereClause = whereClause + "  AND " + criteriaString;

                }

            }


            sqlStatement = selectClause + fromClause + joinStatement + whereClause + orderByClause;

            return sqlStatement;

        }

        private String SponsorsQueryRowNumberSql (List<Data.SortDescriptor> sorts) {

            String sqlString = String.Empty;


            // CUSTOM SORTS TAKE PRECEDENCE OVER DEFAULT SORT

            if (sorts == null) { sorts = new List<Data.SortDescriptor> (); }

            foreach (Data.SortDescriptor currentSort in sorts) {

                switch (currentSort.FieldName.ToLower ()) {

                    default: sqlString = sqlString + currentSort.SqlSortList + ", "; break;

                }

            }


            // DEFAULT SORT 

            // APPEND MEMBER NUMBER AS ALWAYS LAST SORT OPTION

            sqlString = sqlString + "    Sponsor.Id \r\n  ";


            sqlString = "    ROW_NUMBER () OVER (ORDER BY \r\n  " + sqlString;

            sqlString = sqlString + ") AS RowNumber, \r\n  ";

            return sqlString;

        }

        private String SponsorsQueryJoinSqlForSort (List<Data.SortDescriptor> sorts) {

            String joinStatement = String.Empty;


            if (sorts == null) { sorts = new List<Data.SortDescriptor> (); }


            // IDENTIFY ALL RELATED TABLES REQUIRED 

            foreach (Data.SortDescriptor currentSort in sorts) {

                switch (currentSort.FieldName.Split ('.')[0].ToLower ()) {

                    default: break; // PLACE HOLDER DO NOTHING

                }

            }

            return joinStatement;

        }

        public Int32 SponsorsCount (List<Data.FilterDescriptor> filters) {

            Int32 itemCount = 0;


            SetLastException (null);

            try {

                String selectStatement = "SELECT COUNT (1) AS CountOf FROM (" + SponsorsQuerySqlStatement (filters) + ") SourceTable";

                itemCount = Convert.ToInt32 (Application.EnvironmentDatabase.ExecuteScalar (selectStatement));

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }


            return itemCount;

        }

        public List<Sponsor> SponsorsByPage (List<Data.FilterDescriptor> filters, List<Data.SortDescriptor> sorts, Int32 initialRow, Int32 count) {

            String cacheKey = "Application.Sponsor.Id.";

            List<Sponsor> items = new List<Sponsor> ();

            System.Data.DataTable itemTable;

            String sqlStatement = String.Empty;

            String customFilters = String.Empty;

            String customFields = String.Empty;


            ClearLastException ();

            try {

                sqlStatement = sqlStatement + "SELECT SponsorPage.*, Entity.* FROM ( \r\n";

                sqlStatement = sqlStatement + "SELECT \r\n ";

                sqlStatement = sqlStatement + SponsorsQueryRowNumberSql (sorts);

                sqlStatement = sqlStatement + "    Sponsor.* \r\n"; // ADD COMMA IF EXTENDING DATA FIELDS 

                sqlStatement = sqlStatement + "  FROM (" + SponsorsQuerySqlStatement (filters) + ") Sponsor \r\n";

                // ADD JOINS THAT ARE REQUIRED FOR THE SORTING PARAMETERS

                sqlStatement = sqlStatement + SponsorsQueryJoinSqlForSort (sorts);

                sqlStatement = sqlStatement + ") SponsorPage \r\n";

                sqlStatement = sqlStatement + "  JOIN Entity ON SponsorPage.EntityId = Entity.Id \r\n"; // THIS IS TO BRING  IN THE ENTITY DATA FIELDS FOR MAPPING


                sqlStatement = sqlStatement + "  WHERE SponsorPage.RowNumber BETWEEN " + initialRow.ToString ();

                sqlStatement = sqlStatement + " AND (" + initialRow.ToString () + " + " + count.ToString () + " - 1)";


                itemTable = Application.EnvironmentDatabase.SelectDataTable (sqlStatement);

                foreach (System.Data.DataRow currentRow in itemTable.Rows) {

                    Sponsor item = new Core.Sponsor.Sponsor (Application);

                    item.MapDataFields (currentRow);

                    items.Add (item);


                    if (item != null) { // CACHE UNDER ID AND NUMBER WHEN EXISTS

                        CacheManager.CacheObject (cacheKey + item.Id, item, CacheExpirationReference);

                        CacheManager.CacheObject ("Application.Sponsor.Id." + item.Id.ToString (), item, CacheExpirationReference);

                    }

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return items;

        }

        #endregion 

    }

}
